<?php

$sqlserver  = '192.168.1.10';
$sqluser    = 'sa2';
$sqlpassword= 'sasa';
$sqldatabase= 'GT';
$sqlconnection = odbc_connect("Driver={SQL Server};Server=$sqlserver;Database=$sqldatabase;", $sqluser, $sqlpassword);

//需要执行的SQL语句
//单条
//$sql="select id,name from tbl_user where id=1";
//多条数据
//$sql="select id,name from tbl_user";

//$sqlwhere="and U_cardcode in ('".$CardCodeS."')";
$sqlqueryleft1="select  'All' cardcode,'All' cardname,CONVERT(nvarchar(4),DocDate,12) ymonth,sum(profit) sumprofit,sum(linetotal) sumsale,cast(round( sum(profit)/sum(linetotal)*100,2) as numeric(5,2)) profitrate from ax_reportxlite  where 1=1  ";
$sqlqueryright1 =" and docdate>CONVERT(CHAR(10),DATEADD(month,-6,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)group by itemcode,CONVERT(nvarchar(4),DocDate,12) ";

$sqlqueryleft="select  cardcode,[dbo].[BPcontact](cardcode,'CardName') cardname,CONVERT(nvarchar(4),DocDate,12) ymonth,sum(profit) sumprofit,sum(linetotal) sumsale,cast(round( sum(profit)/sum(linetotal)*100,2) as numeric(5,2)) profitrate from ax_reportxlite  where 1=1  ";
$sqlqueryright=" and docdate>CONVERT(CHAR(10),DATEADD(month,-6,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)group by itemcode,cardcode,CONVERT(nvarchar(4),DocDate,12) order by cardcode";
//调用conn.php文件进行数据库操作

//提示操作成功信息，注意：$result存在于conn.php文件中，被调用出来
if (!empty($_GET['itemcode'])) {$itemcode=$_GET['itemcode']; $sqlquerymid=" and itemcode='".$itemcode."'";}
$sqlquery1=$sqlqueryleft1.$sqlquerymid.$sqlqueryright1;
$sqlquery=$sqlquery1." union all ".$sqlqueryleft.$sqlquerymid.$sqlqueryright;

//echo $sqlquery;
$result=odbc_exec($sqlconnection,$sqlquery);
//$result=odbc_fetch_array($result);
// $array=mysql_fetch_array($result,MYSQL_ASSOC);

function gbk2utf8($data){
    if(is_array($data)){
        return array_map('gbk2utf8', $data);
    }
    return iconv('gbk','utf-8',$data);
}


/*数据集*/

$users=array();
$i=0;
while($row=odbc_fetch_array($result)){
    //echo $row['cardcode'].'-----------'.$row['cardname'].'</br>';
    $users[$i]=$row;
    $i++;

}
//print_r($users);
//echo 'A2'.json_encode($users);
if (!empty($itemcode)) {echo $itemcode."(".json_encode(gbk2utf8($users)).")";}

odbc_close($sqlconnection);

/*单条数据*/

//$row=mysql_fetch_row($result,MYSQL_ASSOC);

//echo json_encode(array('jsonObj'=>$row));


//mysql_free_result($result);
//释放结果
//mysql_close();
//关闭连接
